﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Transactions;
//using System.Data.SqlClient;
using System.Configuration;
using MySql.Data.MySqlClient;
using Wheeler.Common.DB;
using Wheeler.Common.Extensions;

namespace Wheeler.Domain
{
    public class UtilityDAO
    {        
        //private SqlConnection _Conn;
        private MySqlConnection _Conn;
        //private DbConnection _Conn;

        public UtilityDAO()
        {
            ///Initialize a DB Connection from the Entity Framework
            WheelerEntities context = new WheelerEntities();
            var entityConnection = context.Connection as System.Data.EntityClient.EntityConnection;
            if (entityConnection != null)
            {
                //_Conn = entityConnection.StoreConnection as MySqlConnection;
                //_Conn = (MySqlConnection)((DbConnection) entityConnection.StoreConnection);
                _Conn = entityConnection.StoreConnection as MySql.Data.MySqlClient.MySqlConnection;
                if (_Conn.State != ConnectionState.Open)
                    _Conn.Open();
            }
        }
        /// <summary>
        /// Gets DataSet by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">The SQL String or the Name of the Stored Procedure</param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetDataSet(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {
            DataSet ds = new DataSet("CustomeQueryResult");
            try
            {
                MySqlCommand command = new MySqlCommand(SQL, _Conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                MySqlDataAdapter da = new MySqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL String Equiped with Page Number PlaceHolders.
        /// Placeholder should be given for Start Record and End Record as {0} -> Start Record and {1} -> End Record
        /// </summary>
        /// <param name="SQL">The SQL String</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, false);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">Name of the Stored Procedure or the Full SQL Statement with the Placeholders for Start Record and End Record</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, isStoredProcedure);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        private DataSet getPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            totalRecord = 0;
            //int startRecord = (pageSize * (pageNumber - 1)) + 1;
            ///This Calculation is alsom MySQL Specific. MySQL Calculates Result set from 0 index instead of 1 as SQL Server
            int startRecord = (pageSize * (pageNumber - 1));
            ///endRecord is not needed in MySQL for Pagination, instead PageSize is required
            //int endRecord = pageSize * pageNumber; 
            DataSet ds = new DataSet("ResultSet");
            try
            {
                MySqlCommand command = new MySqlCommand();
                command.Connection = _Conn;
                if (isStoredProcedure)
                {
                    command.Parameters.AddWithValue("@StartRecord", startRecord);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    SQL = string.Format(SQL, startRecord, pageSize);
                    command.CommandType = CommandType.Text;
                }
                command.CommandText = SQL;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                    {
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));
                    }
                }
                MySqlDataAdapter da = new MySqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            if (ds != null)
            {
                ///MySQL Specific
                if (ds.Tables.Count > 1)
                {
                    object data = ds.Tables[1].Rows[0]["TotalRecord"];
                    totalRecord = ((data == null) || (data == DBNull.Value) || (data.ToString() == string.Empty)) ? 0 : Convert.ToInt32(data);
                }
            }
            return ds;
        }

        /// <summary>
        /// Gets Scalar Value Object by Executing an SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public object GetScalar(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {
            //SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                //if (conn.State != ConnectionState.Open)
                //    conn.Open();

                MySqlCommand command = new MySqlCommand(SQL, _Conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            //return null;
        }

        /// <summary>
        /// Gets a Datatable for a given Lookup table Name
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="valueField"></param>
        /// <param name="textField"></param>
        /// <returns></returns>
        public DataTable GetLookUpData(String tableName, String valueField, String textField)
        {
            DataTable dt = new DataTable();
            DbParameter[] parameters = new[] {
                new DbParameter("@TableName", tableName),
                new DbParameter("@ValueField", valueField),
                new DbParameter("@TextField", textField)
            };
            dt = GetDataSet(DbObject.SP.GET_LOOKUP_DATA, parameters, true).Tables[0];
            return dt;
        }

        /// <summary>
        /// Imports all the data found in a excel shpread sheet in the FixedVahicle table.
        /// Before inserting any record, duplicate checking has been done with RegistrationNo, EngineNo, ChassisNo.
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <param name="userID"></param>
        /// <returns>Returns a String representing total imported record count | total duplicate record count those were ignored</returns>
        public String ImportFixedVehicles(DataTable dt, string fileName, int userID)
        {
            int importCount = 0;
            int duplicateCount = 0;
            int badRecord = 0;
            using (TransactionScope transactionScope = new TransactionScope())
            {
                WheelerEntities context = new WheelerEntities();
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    FixedVehicle fv = new FixedVehicle();
                    fv.RegistrationNo = NullHandler.GetString(row["REGISTRATION_NO"]);
                    fv.EngineNo = NullHandler.GetString(row["ENGINE_NO"]);
                    fv.ChassisNo = NullHandler.GetString(row["CHASSIS_NO"]);
                    fv.ManufactureYear = NullHandler.GetInt(row["MANUFACTURE_YEAR"]);

                    if (fv.RegistrationNo.IsEmpty() || fv.EngineNo.IsEmpty() || fv.ChassisNo.IsEmpty())
                    {
                        badRecord++;
                        continue;
                    }
                    ///Check for Duplicate or previously imported
                    if (context.FixedVehicles.FirstOrDefault(P => String.Compare(P.RegistrationNo, fv.RegistrationNo, true) == 0
                        || String.Compare(P.ChassisNo, fv.ChassisNo, true) == 0
                        || String.Compare(P.EngineNo, fv.EngineNo, true) == 0) != null)
                    {
                        duplicateCount++;
                        continue;
                    }

                    fv.OwnerName = NullHandler.GetString(row["OWNER_NAME"]);
                    if (fv.OwnerName.IsEmpty())
                        fv.OwnerName = "_Unspecified_";
                    String date = NullHandler.GetString(row["REGISTRATION_DATE"]);
                    try
                    {
                        DateTime regDate = DateTime.FromOADate(date.ToDouble());
                        fv.RegistrationDate = regDate > DateTime.MinValue ? regDate : new DateTime(1753, 1, 1);
                    }
                    catch { fv.RegistrationDate = new DateTime(1753, 1, 2); }
                    
                    fv.ImportedOn = DateTime.Now;
                    fv.ImportedBy = userID;
                    fv.FileName = fileName;

                    context.AddToFixedVehicles(fv);
                    importCount++;
                }
                context.SaveChanges();
                transactionScope.Complete();
            }
            return String.Format("{0}|{1}|{2}", importCount, duplicateCount, badRecord);
        }

        /// <summary>
        /// Generates a new Sequence Number for the TestResult Serial Numbering
        /// </summary>
        /// <returns></returns>
        public String NextTestResultSerial(int vehicleID)
        {
            String sequenceName = "TestResultSequence";
            MySqlCommand command = new MySqlCommand("GetNextSequence", _Conn);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new MySqlParameter("@SequenceName", sequenceName));
            command.Parameters.Add(new MySqlParameter("@VehicleID", vehicleID));
            MySqlParameter parameterNewValue = new MySqlParameter("@NewSerial", MySqlDbType.VarChar);
            parameterNewValue.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameterNewValue);
            
            //command.ExecuteNonQuery();
            command.ExecuteScalar();
            String newValue = NullHandler.GetString(parameterNewValue.Value);
            object myValue = command.Parameters["@NewSerial"].Value;
            return newValue;
        }

        /// <summary>
        /// Backup the currently connected database in give file.
        /// Object & Data will be saved in text format.
        /// For details export info visit 
        /// http://www.codeproject.com/Articles/256466/MySqlBackup-NET?fid=1764817&df=90&mpp=10&sort=Position&spc=Tight&tid=4829974
        /// </summary>
        /// <param name="fileName"></param>
        public void BackupDatabase(String fileName)
        {
            using (_Conn)
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = _Conn;
                        mb.ExportToFile(fileName);
                    }
                }
            }
        }
    }
}
